import mysql from 'mysql2/promise'
import { dbConfig } from '../config/database.js'

export const createMatchesTable = async () => {
  try {
    const connection = await mysql.createConnection(dbConfig)
    await connection.query(`
      CREATE TABLE IF NOT EXISTS matches (
        id INT PRIMARY KEY AUTO_INCREMENT,
        match_date DATE NOT NULL,
        match_time TIME NOT NULL,
        week_number INT,
        venue_id INT,
        match_schedule_id INT,
        home_school_id INT NOT NULL,
        away_school_id INT NOT NULL,
        home_group_id INT,
        away_group_id INT,
        status VARCHAR(20) DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (venue_id) REFERENCES venues(id),
        FOREIGN KEY (match_schedule_id) REFERENCES match_schedules(id),
        FOREIGN KEY (home_school_id) REFERENCES schools(id),
        FOREIGN KEY (away_school_id) REFERENCES schools(id),
        FOREIGN KEY (home_group_id) REFERENCES schedule_groups(id),
        FOREIGN KEY (away_group_id) REFERENCES schedule_groups(id)
      )
    `)
    await connection.end()
    console.log('Matches table created successfully')
  } catch (error) {
    console.error('Error creating matches table:', error)
    throw error
  }
} 